const mysql = require('mysql2/promise');
const config = require('./database-config.js');

/**
 * 宝石系统数据导入脚本
 * 根据宝石系统实现完成报告中的属性上限表导入数据
 */

// 宝石基础数据（15种宝石）
const gemBaseData = [
    { id: 1, name: '钻石', image: 'diamond.png', maxLevel: 10 },
    { id: 2, name: '水晶', image: 'crystal.png', maxLevel: 10 },
    { id: 3, name: '玛瑙', image: 'agate.png', maxLevel: 10 },
    { id: 4, name: '白玉', image: 'white_jade.png', maxLevel: 10 },
    { id: 5, name: '琥珀', image: 'amber.png', maxLevel: 10 },
    { id: 6, name: '翡翠', image: 'jadeite.png', maxLevel: 10 },
    { id: 7, name: '珍珠', image: 'pearl.png', maxLevel: 10 },
    { id: 8, name: '绿宝石', image: 'emerald.png', maxLevel: 10 },
    { id: 9, name: '红宝石', image: 'ruby.png', maxLevel: 10 },
    { id: 10, name: '蓝宝石', image: 'sapphire.png', maxLevel: 10 },
    { id: 11, name: '猫眼石', image: 'cat_eye.png', maxLevel: 10 },
    { id: 12, name: '金刚玉', image: 'adamantite.png', maxLevel: 10 },
    { id: 13, name: '龙血石', image: 'dragon_blood.png', maxLevel: 10 },
    { id: 14, name: '黑曜石', image: 'obsidian.png', maxLevel: 10 },
    { id: 15, name: '乾坤珠', image: 'qiankun_pearl.png', maxLevel: 10 }
];

// 宝石属性数据（根据文档中的属性上限表）
const gemAttributesData = [
    // 蓝宝石 - 体质
    { gemId: 10, attributeName: '体质', equipmentSlots: '1,3', maxValue: 15 },

    // 猫眼石 - 智力
    { gemId: 11, attributeName: '智力', equipmentSlots: '1,4', maxValue: 15 },

    // 水晶 - 力量
    { gemId: 2, attributeName: '力量', equipmentSlots: '2,4,3', maxValue: 15 },

    // 玛瑙 - 敏捷
    { gemId: 3, attributeName: '敏捷', equipmentSlots: '2,6', maxValue: 15 },

    // 白玉 - 气血
    { gemId: 4, attributeName: '气血', equipmentSlots: '1,5', maxValue: 1500 },

    // 琥珀 - 精力
    { gemId: 5, attributeName: '精力', equipmentSlots: '1,4', maxValue: 1500 },

    // 翡翠、玛瑙 - 攻击
    { gemId: 6, attributeName: '攻击', equipmentSlots: '2,5', maxValue: 750 },
    { gemId: 3, attributeName: '攻击', equipmentSlots: '2,5', maxValue: 750 },

    // 珍珠、白玉 - 防御
    { gemId: 7, attributeName: '防御', equipmentSlots: '1,3', maxValue: 750 },
    { gemId: 4, attributeName: '防御', equipmentSlots: '1,3', maxValue: 750 },

    // 钻石 - 速度
    { gemId: 1, attributeName: '速度', equipmentSlots: '2,6', maxValue: 15 },

    // 珍珠 - 命中率
    { gemId: 7, attributeName: '命中率', equipmentSlots: '1,2,4,5', maxValue: 15 },

    // 钻石、金刚玉 - 暴击率
    { gemId: 1, attributeName: '暴击率', equipmentSlots: '2,5,6', maxValue: 15 },
    { gemId: 12, attributeName: '暴击率', equipmentSlots: '2,5,6', maxValue: 15 },

    // 绿宝石 - 反击率
    { gemId: 8, attributeName: '反击率', equipmentSlots: '1,3,6', maxValue: 15 },

    // 红宝石 - 致命率
    { gemId: 9, attributeName: '致命率', equipmentSlots: '2,4,5,3', maxValue: 15 },

    // 琥珀 - 反震率
    { gemId: 5, attributeName: '反震率', equipmentSlots: '1,3,6', maxValue: 15 },

    // 翡翠 - 躲避率
    { gemId: 6, attributeName: '躲避率', equipmentSlots: '1,3,6', maxValue: 15 },

    // 龙血石 - 暴击力
    { gemId: 13, attributeName: '暴击力', equipmentSlots: '1,2,5', maxValue: 15 },

    // 金刚玉 - 穿透率（特殊限制：只能镶嵌在武器上）
    { gemId: 12, attributeName: '穿透率', equipmentSlots: '2', maxValue: 15 },

    // 龙血石、黑曜石 - 法爆率
    { gemId: 13, attributeName: '法爆率', equipmentSlots: '2,5', maxValue: 12 },
    { gemId: 14, attributeName: '法爆率', equipmentSlots: '2,5', maxValue: 12 },

    // 龙血石 - 爆伤力
    { gemId: 13, attributeName: '爆伤力', equipmentSlots: '2,5', maxValue: 15 },

    // 黑曜石 - 法伤力
    { gemId: 14, attributeName: '法伤力', equipmentSlots: '2,5', maxValue: 10 },

    // 黑曜石 - 法穿率
    { gemId: 14, attributeName: '法穿率', equipmentSlots: '2,5', maxValue: 10 },

    // 钻石、水晶 - 抗物理
    { gemId: 1, attributeName: '抗物理', equipmentSlots: '1,3,5', maxValue: 15 },
    { gemId: 2, attributeName: '抗物理', equipmentSlots: '1,3,5', maxValue: 15 },

    // 绿宝石、琥珀 - 抗围困
    { gemId: 8, attributeName: '抗围困', equipmentSlots: '1,3,5', maxValue: 15 },
    { gemId: 5, attributeName: '抗围困', equipmentSlots: '1,3,5', maxValue: 15 },

    // 红宝石、翡翠 - 抗扰乱
    { gemId: 9, attributeName: '抗扰乱', equipmentSlots: '1,3,5', maxValue: 15 },
    { gemId: 6, attributeName: '抗扰乱', equipmentSlots: '1,3,5', maxValue: 15 },

    // 珍珠、蓝宝石 - 抗封锁
    { gemId: 7, attributeName: '抗封锁', equipmentSlots: '1,3,5', maxValue: 15 },
    { gemId: 10, attributeName: '抗封锁', equipmentSlots: '1,3,5', maxValue: 15 },

    // 绿宝石、猫眼石 - 抗风沙
    { gemId: 8, attributeName: '抗风沙', equipmentSlots: '1,3,5', maxValue: 15 },
    { gemId: 11, attributeName: '抗风沙', equipmentSlots: '1,3,5', maxValue: 15 },

    // 红宝石、水晶 - 抗妖火
    { gemId: 9, attributeName: '抗妖火', equipmentSlots: '1,3,5', maxValue: 15 },
    { gemId: 2, attributeName: '抗妖火', equipmentSlots: '1,3,5', maxValue: 15 },

    // 蓝宝石、玛瑙 - 抗落雷
    { gemId: 10, attributeName: '抗落雷', equipmentSlots: '1,3,5', maxValue: 15 },
    { gemId: 3, attributeName: '抗落雷', equipmentSlots: '1,3,5', maxValue: 15 },

    // 白玉、猫眼石 - 抗毒术
    { gemId: 4, attributeName: '抗毒术', equipmentSlots: '1,3,5', maxValue: 15 },
    { gemId: 11, attributeName: '抗毒术', equipmentSlots: '1,3,5', maxValue: 15 },

    // 乾坤珠 - 连击率、连击数、暴击率
    { gemId: 15, attributeName: '连击率', equipmentSlots: '2,5', maxValue: 12 },
    { gemId: 15, attributeName: '连击数', equipmentSlots: '2,5', maxValue: 3 },
    { gemId: 15, attributeName: '暴击率', equipmentSlots: '2,5,6', maxValue: 15 }
];

async function importGemSystemData() {
    let connection;

    try {
        console.log('🔮 开始导入宝石系统数据...');

        // 连接数据库
        connection = await mysql.createConnection(config.source);
        console.log('✅ 数据库连接成功');

        // 1. 创建宝石基础表
        console.log('\n1️⃣ 创建宝石基础表...');
        const createGemBaseTableSQL = `
      CREATE TABLE IF NOT EXISTS \`gem_base\` (
        \`id\` int NOT NULL AUTO_INCREMENT COMMENT '宝石ID',
        \`name\` varchar(50) NOT NULL COMMENT '宝石名称',
        \`image\` varchar(100) DEFAULT NULL COMMENT '宝石图片',
        \`max_level\` int NOT NULL DEFAULT 10 COMMENT '最大等级',
        \`created_at\` datetime NOT NULL COMMENT '创建时间',
        \`updated_at\` datetime DEFAULT NULL COMMENT '更新时间',
        PRIMARY KEY (\`id\`),
        UNIQUE KEY \`uk_name\` (\`name\`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='宝石基础表';
    `;
        await connection.execute(createGemBaseTableSQL);
        console.log('✅ 宝石基础表创建成功');

        // 2. 创建宝石属性表
        console.log('\n2️⃣ 创建宝石属性表...');
        const createGemAttributesTableSQL = `
      CREATE TABLE IF NOT EXISTS \`gem_attributes\` (
        \`id\` int NOT NULL AUTO_INCREMENT COMMENT '属性ID',
        \`gem_id\` int NOT NULL COMMENT '宝石ID',
        \`attribute_name\` varchar(50) NOT NULL COMMENT '属性名称',
        \`equipment_slots\` varchar(50) NOT NULL COMMENT '可镶嵌装备槽位，1头盔 2武器 3铠甲 4项链 5护腕 6鞋子',
        \`max_value\` int NOT NULL COMMENT '最大属性值',
        \`created_at\` datetime NOT NULL COMMENT '创建时间',
        \`updated_at\` datetime DEFAULT NULL COMMENT '更新时间',
        PRIMARY KEY (\`id\`),
        KEY \`idx_gem_id\` (\`gem_id\`),
        KEY \`idx_attribute_name\` (\`attribute_name\`),
        CONSTRAINT \`fk_gem_attributes_gem_id\` FOREIGN KEY (\`gem_id\`) REFERENCES \`gem_base\` (\`id\`) ON DELETE CASCADE
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='宝石属性表';
    `;
        await connection.execute(createGemAttributesTableSQL);
        console.log('✅ 宝石属性表创建成功');

        // 3. 清空现有数据
        console.log('\n3️⃣ 清空现有数据...');
        await connection.execute('DELETE FROM `gem_attributes`');
        await connection.execute('DELETE FROM `gem_base`');
        console.log('✅ 现有数据清空完成');

        // 4. 导入宝石基础数据
        console.log('\n4️⃣ 导入宝石基础数据...');
        const insertGemBaseSQL = `
      INSERT INTO \`gem_base\` (\`id\`, \`name\`, \`image\`, \`max_level\`, \`created_at\`) 
      VALUES (?, ?, ?, ?, NOW())
    `;

        for (const gem of gemBaseData) {
            await connection.execute(insertGemBaseSQL, [gem.id, gem.name, gem.image, gem.maxLevel]);
        }
        console.log(`✅ 成功导入 ${gemBaseData.length} 种宝石基础数据`);

        // 5. 导入宝石属性数据
        console.log('\n5️⃣ 导入宝石属性数据...');
        const insertGemAttributesSQL = `
      INSERT INTO \`gem_attributes\` (\`gem_id\`, \`attribute_name\`, \`equipment_slots\`, \`max_value\`, \`created_at\`) 
      VALUES (?, ?, ?, ?, NOW())
    `;

        for (const attr of gemAttributesData) {
            await connection.execute(insertGemAttributesSQL, [attr.gemId, attr.attributeName, attr.equipmentSlots, attr.maxValue]);
        }
        console.log(`✅ 成功导入 ${gemAttributesData.length} 条宝石属性数据`);

        // 6. 验证数据
        console.log('\n6️⃣ 验证导入数据...');

        // 验证宝石基础数据
        const [gemBaseCount] = await connection.execute('SELECT COUNT(*) as count FROM `gem_base`');
        console.log(`📊 宝石基础表记录数: ${gemBaseCount[0].count}`);

        // 验证宝石属性数据
        const [gemAttributesCount] = await connection.execute('SELECT COUNT(*) as count FROM `gem_attributes`');
        console.log(`📊 宝石属性表记录数: ${gemAttributesCount[0].count}`);

        // 显示部分数据示例
        console.log('\n📋 宝石基础数据示例:');
        const [gemBaseSample] = await connection.execute('SELECT * FROM `gem_base` LIMIT 5');
        gemBaseSample.forEach(gem => {
            console.log(`  - ${gem.name} (ID:${gem.id}): 图片=${gem.image}, 最大等级=${gem.max_level}`);
        });

        console.log('\n📋 宝石属性数据示例:');
        const [gemAttributesSample] = await connection.execute(`
      SELECT ga.*, gb.name as gem_name 
      FROM \`gem_attributes\` ga 
      JOIN \`gem_base\` gb ON ga.gem_id = gb.id 
      LIMIT 10
    `);
        gemAttributesSample.forEach(attr => {
            console.log(`  - ${attr.gem_name}: ${attr.attribute_name} (最大${attr.max_value}, 槽位${attr.equipment_slots})`);
        });

        console.log('\n🎉 宝石系统数据导入完成！');
        console.log('\n📊 导入统计:');
        console.log(`  - 宝石类型: ${gemBaseData.length} 种`);
        console.log(`  - 宝石属性: ${gemAttributesData.length} 条`);
        console.log(`  - 装备槽位: 6 种 (1头盔 2武器 3铠甲 4项链 5护腕 6鞋子)`);
        console.log(`  - 属性类型: 29 种不同属性组合`);

    } catch (error) {
        console.error('❌ 导入失败:', error.message);
        if (error.code) {
            console.error('错误代码:', error.code);
        }
        throw error;
    } finally {
        if (connection) {
            await connection.end();
            console.log('🔌 数据库连接已关闭');
        }
    }
}

// 执行导入
if (require.main === module) {
    importGemSystemData()
        .then(() => {
            console.log('\n✅ 宝石系统数据导入脚本执行完成');
            process.exit(0);
        })
        .catch((error) => {
            console.error('\n❌ 宝石系统数据导入脚本执行失败:', error.message);
            process.exit(1);
        });
}

module.exports = { importGemSystemData, gemBaseData, gemAttributesData };
